'''
数据分析：
    1.统计表格中有多少人
    2.统计电信、联通、移动用户数量占比
    3.总公司男女人数
    4.年龄超过45岁的老员工人数
    5.薪资高于8000人数和低于3000人数
    6.统计去传媒公司工作的人数
    7.统计可能在疫情高纬地区的人数（黑龙江、北京、福建、四川）
'''
import xlrd#大佬处理excel的包

#打开文件表格
wb = xlrd.open_workbook(filename=r"D:\软件测试\微信文件\python基础部分和单元测试的开发\5.baidu-员工的人员信息.xls")
sheet = wb.sheet_by_index(0)

#表格里总共多少人
print("总共：",sheet.nrows-1,"个人")

# 统计移动、联通、电信用户数量及占比
mobile_prefixes = ('134', '135', '136', '137', '138', '139', '147', '150', '151', '152', '157', '158', '159', '178', '182', '183', '184', '187', '188', '1703', '1705', '1706')
unicom_prefixes = ('130', '131', '132', '145', '155', '156', '175', '176', '185', '186', '1704', '1707', '1708', '1709', '171')
telecom_prefixes = ('133', '149', '153', '173', '177', '180', '181', '189', '1700', '1701', '1702')
mobile_count = 0
unicom_count = 0
telecom_count = 0
for row_index in range(1, sheet.nrows):  # 跳过表头行
    phone_number = str(sheet.cell_value(row_index, 5))#cell单元格索引（所有行，六列）
    if phone_number.startswith(mobile_prefixes):#startswith字符串方法，用于判断字符串是否以指定的前缀开头
        mobile_count += 1
    elif phone_number.startswith(unicom_prefixes):
        unicom_count += 1
    elif phone_number.startswith(telecom_prefixes):
        telecom_count += 1
total_count = mobile_count + unicom_count + telecom_count
mobile_pct = round(mobile_count / total_count * 100, 2)
unicom_pct = round(unicom_count / total_count * 100, 2)
telecom_pct = round(telecom_count / total_count * 100, 2)
print(f"移动用户数量: {mobile_count}, 占比: {mobile_pct}%")
print(f"联通用户数量: {unicom_count}, 占比: {unicom_pct}%")
print(f"电信用户数量: {telecom_count}, 占比: {telecom_pct}%")

# 统计性别人数
male_count = 0
female_count = 0
for row_index in range(1, sheet.nrows):  # 跳过表头行
    gender = sheet.cell_value(row_index, 8)
    if gender == '男':
        male_count += 1
    elif gender == '女':
        female_count += 1
print(f"男性人数: {male_count}, 女性人数: {female_count}")

# 统计年龄超过45岁的员工人数
old_count = 0
for row_index in range(1, sheet.nrows):  # 跳过表头行
    age = sheet.cell_value(row_index, 7)  # 假设年龄在第4列
    if age > 45:
        old_count += 1
print(f"年龄超过45岁的员工人数: {old_count}")

# 统计薪资情况
high_salary_count = 0
low_salary_count = 0
for row_index in range(1, sheet.nrows):  # 跳过表头行
    salary = sheet.cell_value(row_index, 11)
    if salary > 8000:
        high_salary_count += 1
    elif salary < 3000:
        low_salary_count += 1
print(f"薪资高于8000元的高薪人员数量: {high_salary_count}")
print(f"薪资低于3000元的底薪人员数量: {low_salary_count}")

# 统计在传媒公司工作的人员数量
media_count = 0
for row_index in range(1, sheet.nrows):  # 跳过表头行
    company = str(sheet.cell_value(row_index, 13))
    if '传媒' in company:
        media_count += 1
print(f"去传媒公司工作的人员数量: {media_count}")

# 统计在疫情高危地区的人数
high_risk_areas = ['黑龙江', '北京', '福建', '四川']
high_risk_count = 0
for row_index in range(1, sheet.nrows):  # 跳过表头行
    address = str(sheet.cell_value(row_index, 9))
    for area in high_risk_areas:
        if area in address:
            high_risk_count += 1
            break
print(f"可能在疫情高危地区的人数: {high_risk_count}")